const sqlite3 = require('sqlite3').verbose();
const path = require('path');

class TodoDatabase {
    constructor(db) {
        this.db = db;
    }

    async createTodosTable() {
        const createTodosTable = `
            CREATE TABLE IF NOT EXISTS todos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                widget_id TEXT NOT NULL,
                text TEXT NOT NULL,
                priority TEXT NOT NULL DEFAULT 'medium',
                completed INTEGER NOT NULL DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (widget_id) REFERENCES widgets (id) ON DELETE CASCADE
            )
        `;
        
        return new Promise((resolve, reject) => {
            this.db.run(createTodosTable, (err) => {
                if (err) {
                    reject(err);
                } else {
                    resolve();
                }
            });
        });
    }

    async addTodo(widgetId, text, priority = 'medium') {
        return new Promise((resolve, reject) => {
            const stmt = this.db.prepare(`
                INSERT INTO todos (widget_id, text, priority, completed)
                VALUES (?, ?, ?, 0)
            `);
            
            stmt.run([widgetId, text, priority], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve({
                        id: this.lastID.toString(),
                        text,
                        priority,
                        completed: false,
                        createdAt: new Date().toISOString()
                    });
                }
            });
            stmt.finalize();
        });
    }

    async getTodos(widgetId) {
        return new Promise((resolve, reject) => {
            this.db.all(
                'SELECT * FROM todos WHERE widget_id = ? ORDER BY created_at DESC',
                [widgetId],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        const todos = rows.map(row => ({
                            id: row.id.toString(),
                            text: row.text,
                            priority: row.priority,
                            completed: row.completed === 1,
                            createdAt: row.created_at
                        }));
                        resolve(todos);
                    }
                }
            );
        });
    }

    async toggleTodo(todoId) {
        return new Promise((resolve, reject) => {
            this.db.run(
                'UPDATE todos SET completed = NOT completed, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
                [todoId],
                function(err) {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(this.changes > 0);
                    }
                }
            );
        });
    }

    async deleteTodo(todoId) {
        return new Promise((resolve, reject) => {
            this.db.run('DELETE FROM todos WHERE id = ?', [todoId], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.changes > 0);
                }
            });
        });
    }

    async getTodoStats(widgetId) {
        return new Promise((resolve, reject) => {
            this.db.all(
                'SELECT completed, COUNT(*) as count FROM todos WHERE widget_id = ? GROUP BY completed',
                [widgetId],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        const stats = { total: 0, completed: 0, pending: 0 };
                        rows.forEach(row => {
                            stats.total += row.count;
                            if (row.completed === 1) {
                                stats.completed = row.count;
                            } else {
                                stats.pending = row.count;
                            }
                        });
                        resolve(stats);
                    }
                }
            );
        });
    }

    async updateTodoText(todoId, text) {
        return new Promise((resolve, reject) => {
            this.db.run(
                'UPDATE todos SET text = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
                [text, todoId],
                function(err) {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(this.changes > 0);
                    }
                }
            );
        });
    }

    async updateTodoPriority(todoId, priority) {
        return new Promise((resolve, reject) => {
            this.db.run(
                'UPDATE todos SET priority = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
                [priority, todoId],
                function(err) {
                    if (err) {
                        reject(err);
                    } else {
                        resolve(this.changes > 0);
                    }
                }
            );
        });
    }
}

module.exports = TodoDatabase;